package com.hnkywl.sts.dao.tjbb;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: SDE09
 * Date: 13-9-28
 * Time: 下午5:14
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class GyswlDao extends SimpleHibernateTemplate {
    /**
     * 财务收发存报表
     *
     * @param gsid
     * @param
     * @param
     * @return
     */
    public Map<String, Object> gyswlList(Integer gsid, Integer deptid, Date enddt, String custname, Integer pageNo, Integer pageSize, Integer tc_flag) {
        //1、gs_id 若为空  则等于 -1
        if (gsid == null)
            gsid = -1;
        Integer v_gsid;
        v_gsid = gsid;
//        if (deptid == null)
        deptid = -1;
        Integer v_deptid;
        v_deptid = deptid;
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(enddt);
        //calendar.add(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
        java.sql.Date paramDate = new java.sql.Date(calendar.getTime().getTime());
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call P_RPT_GYSWL(?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, gsid);
            call.setInt(2, deptid);
            call.setDate(3, paramDate);
            call.setString(4, custname);
            call.setInt(5, (pageNo - 1) * pageSize);
            call.setInt(6, pageNo * pageSize);
            call.setInt(7, tc_flag);
            call.registerOutParameter(8, Types.NUMERIC);
            call.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            total = call.getInt(8);
            rs = ((OracleCallableStatement) call).getCursor(9);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if (!"合计".equals(r.get("gs_name").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("total", total);
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    public Map<String, List<Map>> gyswlListForExport(Integer gsid, Integer deptid, Date enddt, String custname, Integer pageNo, Integer pageSize, Integer tc_flag) {
        //1、gs_id 若为空  则等于 -1
        if (gsid == null)
            gsid = -1;
        Integer v_gsid;
        v_gsid = gsid;
//        if (deptid == null)
        deptid = -1;
        Integer v_deptid;
        v_deptid = deptid;
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(enddt);
        calendar.add(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
        java.sql.Date paramDate = new java.sql.Date(calendar.getTime().getTime());
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call P_RPT_GYSWL(?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, gsid);
            call.setInt(2, deptid);
            call.setDate(3, paramDate);
            call.setString(4, custname);
            call.setInt(5, (pageNo - 1) * pageSize);
            call.setInt(6, pageNo * pageSize);
            call.setInt(7, tc_flag);
            call.registerOutParameter(8, Types.NUMERIC);
            call.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(9);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, List<Map>> result = new HashMap<String, List<Map>>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if (!"合计".equals(r.get("gs_name").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }

    /**
     * 供应商往来财务业务收付款调用存储过程
     *
     * @param gsid
     * @param enddt
     * @param type
     * @param type
     * @return
     */
    public Map<String, Object> gysWlDzList(Integer gsid, Integer deptid, Date begindt, Date enddt, String custname, Integer type, Integer pageNo, Integer pageSize) {
        //1、gs_id 若为空  则等于 -1
        if (gsid == null)
            gsid = -1;
        Integer v_gsid;
        v_gsid = gsid;
        deptid = -1;
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(enddt);
        //calendar.add(Calendar.DAY_OF_MONTH, calendar.getActualMinimum(Calendar.DAY_OF_MONTH));
        java.sql.Date paramDate = new java.sql.Date(calendar.getTime().getTime());
        //=============取当月1号==============
        java.sql.Date startDate;
        if (begindt == null) {
            Calendar startCal = Calendar.getInstance();
            startCal.setTime(new Date());
            startCal.set(Calendar.DAY_OF_MONTH, 1);
            startDate = new java.sql.Date(startCal.getTime().getTime());
        } else {
            Calendar calendar1 = Calendar.getInstance();
            calendar1.setTime(begindt);
            // calendar1.add(calendar1.DAY_OF_MONTH, calendar1.getActualMinimum(calendar1.DAY_OF_MONTH));
            startDate = new java.sql.Date(calendar1.getTime().getTime());
        }

        //=============================
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        Integer total = 0;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call P_RPT_GYSWL_WLDZ(?,?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, gsid);
            call.setInt(2, deptid);
            call.setDate(3, startDate);
            call.setDate(4, paramDate);
            call.setString(5, custname);
            call.setInt(6, type);
            call.setInt(7, (pageNo - 1) * pageSize);
            call.setInt(8, pageNo * pageSize);
            call.registerOutParameter(9, Types.NUMERIC);
            call.registerOutParameter(10, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            total = call.getInt(9);
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(10);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            Map<String, Object> result = new HashMap<String, Object>();
            List<Map> rows = new ArrayList<Map>();
            List<Map> footer = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                if (!"合计".equals(r.get("dt").toString()))
                    rows.add(r);
                else
                    footer.add(r);
            }
            result.put("total", total);
            result.put("rows", rows);
            result.put("footer", footer);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }


    /**
     * 采购未结算金额
     *
     * @param gsid
     * @param enddt
     * @param custname
     * @return
     */
    public List<Map> cgwjsje(Integer gsid, Date enddt, String custname) {
        if (custname == null || "".equals(custname))
            custname = "-1";
        String sql = "select gs_name," +
                "            cgdh," +
                "           to_char(rksj,'yyyy-mm-dd')  rksj," +
                "          wzbh," +
                "         pm," +
                "         ggxh," +
                "          sl," +
                "          dj," +
                "          je," +
                "          cust_name  \n" +
                "   from v_rpt_gyswl_cgwjs " +
                "  where  gs_id = :gs_id " +
                "  and dt <= :endDate\n" +
                "  and cust_name like :cust_name \n" +
                "  union all\n" +
                "  select " +
                "   '合计:', " +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   null," +
                "   sum(sl)," +
                "   null," +
                "   sum(je)," +
                "   null    \n" +
                "  from v_rpt_gyswl_cgwjs " +
                "  where  gs_id = :gs_id " +
                "  and dt <= :endDate\n" +
                "  and cust_name like :cust_name \n";
        SQLQuery sqlQuery = createSQLQuery(sql);
        sqlQuery.setParameter("gs_id", gsid);
        sqlQuery.setParameter("endDate", enddt);
        //sqlQuery.setParameter("cust_name", custname);   "%"+custname+"%"
        sqlQuery.setParameter("cust_name", "%" + custname + "%");
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }


}
